﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Text;
using Framework;

namespace Enterprise
{
    public class ArticleCategoryService : BaseService
    {

        /// <summary>
        /// 获取列表
        /// </summary>
        /// <returns></returns>
        public Table GetTable()
        {
            var table = new Table
            {
                Rows = DbHelperOleDb.Query("select * from tbarticlecategory  order by cosort desc").Tables["ds"].Rows,
                Total = DbHelperOleDb.GetSingle("select count(*) from tbarticlecategory").SafeToInt()
            };
            return table;
        }
        /// <summary>
        /// 获取表格
        /// </summary>
        /// <returns></returns>
        public DataTable GetDataTable()
        {
            return DbHelperOleDb.Query("select * from tbarticlecategory  order by cosort desc").Tables["ds"];
        }

        /// <summary>
        /// 获取表格
        /// </summary>
        /// <returns></returns>
        public DataTable GetDataTable(int parentId)
        {
            return DbHelperOleDb.Query("select * from tbarticlecategory where CoParentId={0} order by cosort desc".FormatWith(parentId)).Tables["ds"];
        }
        /// <summary>
        /// 根据ID获取一条记录
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataRow GetById(int id)
        {
            return DbHelperOleDb.Query("select A.CoName,A.CoNameEn,A.CoArticleCategoryId,A.CoSort,B.CoName as CoParentName  from tbarticlecategory as A left join tbarticlecategory as B on A.CoParentId=B.coarticlecategoryId where A.coarticlecategoryId={0}".FormatWith(id)).Tables["ds"].Rows[0];
        }


        /// <summary>
        /// 添加文章分类
        /// </summary>
        /// <param name="name">分类名称</param>
        /// <param name="nameEn">分类名称(EN)</param>
        /// <param name="parentId">父级ID</param>
        /// <param name="sort">排序</param>
        public int Add(string name, string nameEn, int parentId, int sort)
        {
            Check.Argument.IsNotOutOfLength(name, 2, 50, "分类名称");
            Check.Argument.IsNotOutOfLength(nameEn, 50, "分类名称(En)");
            var sbSql = new StringBuilder();
            sbSql.Append("insert into tbarticlecategory(");
            sbSql.Append("CoName,CoNameEn,CoCategoryPath,CoParentId,CoDepth,CoType,CoLanguage,CoSort");
            sbSql.Append(")");
            sbSql.Append(" values (");
            sbSql.Append("@CoName,@CoNameEn,@CoCategoryPath,@CoParentId,@CoDepth,@CoType,@CoLanguage,@CoSort");
            sbSql.Append(")");
            OleDbParameter[] parameters =
                {
                    new OleDbParameter("@CoName",OleDbType.VarChar, 255),
                    new OleDbParameter("@CoNameEn",OleDbType.VarChar,255),
                    new OleDbParameter("@CoCategoryPath",OleDbType.LongVarWChar),
                    new OleDbParameter("@CoParentId", OleDbType.Integer),
                    new OleDbParameter("@CoDepth",OleDbType.Integer),
                    new OleDbParameter("@CoType",OleDbType.Integer),
                    new OleDbParameter("@CoLanguage",OleDbType.VarChar, 10),
                    new OleDbParameter("@CoSort",OleDbType.Integer)
               };
            parameters[0].Value = name;
            parameters[1].Value = nameEn;
            parameters[2].Value = "";
            parameters[3].Value = parentId;
            parameters[4].Value = 1;
            parameters[5].Value = 0;
            parameters[6].Value = DBNull.Value;
            parameters[7].Value = sort;
            using (var sdr = DbHelperOleDb.ExecuteReader("select * from tbarticlecategory where CoarticleCategoryId={0}".FormatWith(parentId)))
            {
                if (sdr.Read())
                {
                    var depth = sdr["CoDepth"].SafeToInt() + 1;
                    if (ArticleCategoryConfig.Depth < depth)
                    {
                        throw new Exception("分类深度不能超过{0}级!".FormatWith(ArticleCategoryConfig.Depth));
                    }
                    parameters[2].Value = sdr["CoCategoryPath"].SafeToString() + parentId + ",";
                    parameters[3].Value = parentId;
                    parameters[4].Value = depth;

                }
                else
                {
                    parameters[3].Value = DBNull.Value;
                }
            }
            var affecteds = DbHelperOleDb.Add(sbSql.ToString(), parameters);
            return affecteds;

        }
        /// <summary>
        /// 编辑文章分类
        /// </summary>
        /// <param name="id"></param>
        /// <param name="name"></param>
        /// <param name="nameEn"></param>
        /// <param name="sort"></param>
        public void Edit(int id, string name, string nameEn, int sort)
        {
            Check.Argument.IsNotOutOfLength(name, 2, 50, "分类名称");
            Check.Argument.IsNotOutOfLength(nameEn, 50, "分类名称(En)");
            var sbSql = new StringBuilder();
            sbSql.Append("update tbarticlecategory set ");
            sbSql.Append(" CoName=@CoName,");
            sbSql.Append(" CoNameEn=@CoNameEn,");
            sbSql.Append(" CoSort=@CoSort ");
            sbSql.AppendFormat(" where coarticlecategoryid={0} ", id);
            OleDbParameter[] parameters =
                {
                    new OleDbParameter("@CoName", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoNameEn", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoSort", OleDbType.Integer)
                };
            parameters[0].Value = name;
            parameters[1].Value = nameEn;
            parameters[2].Value = sort;

            var obj = DbHelperOleDb.ExecuteSql(sbSql.ToString(), parameters).SafeToInt();
            if (!(obj > 0))
            {
                throw new Exception("记录不存在！");
            }
        }
        /// <summary>
        /// 删除文章分类
        /// </summary>
        /// <param name="id"></param>
        public void Delete(int id)
        {
            var syscount = DbHelperOleDb.GetSingle("select count(*) from tbarticlecategory  where coarticlecategoryid={0} and coissystem=1".FormatWith(id)).SafeToInt();
            if (syscount > 0)
            {
                throw new Exception("系统项不能删除！");
            }
            var childcount = DbHelperOleDb.GetSingle("select count(*) from tbarticlecategory  where coparentid={0}".FormatWith(id)).SafeToInt();
            if (childcount > 0)
            {
                throw new Exception("该分类存在子类，请先删除子类！");
            }
            var count = DbHelperOleDb.GetSingle("select count(*) from tbarticle  where coarticlecategoryid={0}".FormatWith(id)).SafeToInt();
            if (count > 0)
            {
                throw new Exception("该分类下存在文章，请先删除文章记录！");
            }
            var obj = DbHelperOleDb.ExecuteSql("delete from tbarticlecategory where coarticlecategoryid={0}".FormatWith(id)).SafeToInt();
            if (!(obj > 0))
            {
                throw new Exception("记录不存在！");
            }
        }

    }
}
